package com.ivo.quartz;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ivo.biz.Dustfree;
import com.ivo.biz.DustfreeManager;
import com.ivo.biz.PerformanceMon;
import com.ivo.biz.PerformanceService;
import com.ivo.core.hibernate.HibernateInvoker;
import com.ivo.util.JdbcUtil;

/**
 * 
 *
 * @author Minkey Wang
 * @version 2017年10月20日
 */
@Repository
public class DustfreeQuartz {
	
	@Resource(name = "dsEdw")
	private DataSource dsEdwDataSource;

    @Autowired
    private PerformanceService performanceService;
	
    @Autowired
    private HibernateInvoker hibernateInvoker;
    
    @Autowired
    private DustfreeManager dustfreeManager;
    
	protected Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Transactional
	@Scheduled(cron = "0 30 7 16 * ? ")//每月16日上午07:30触发
	public void execute() {
		logger.info("DustfreeQuartz 开始同步******************************************");
		SimpleDateFormat sdf =  new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance(); 
		String str = sdf.format(calendar.getTime());
		String month = str.substring(0, 7);
//		String month ="2018-04";
		calendar.add(Calendar.MONTH, -1);    //得到前一个月   
		String str1 = sdf.format(calendar.getTime());
		String month1 = str1.substring(0, 7);
		String fromDate =month1+"-16";
		String toDate =month+"-16";
		
		performanceService.deleteDustfreeBymon(month);
		
		logger.info("同步ary和cel厂的数据开始******************************************");
		StringBuilder selectSQL = new StringBuilder();
		selectSQL.append("select FAB_CODE,USER_CODE,USER_NAME,USER_DEPT, ")
				.append("sum(to_number(TO_DATE(to_char(OUT_TIME,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss')-TO_DATE(to_char(ENTER_TIME,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))*24)  HOURS ")
				.append("from SOR.WHR_FAB_ENTEROUT ")
				.append("WHERE ENTER_TIME >= TO_DATE(?,'YYYY-MM-DD hh24:mi:ss') ")
				.append("and    ENTER_TIME < TO_DATE(?,'YYYY-MM-DD hh24:mi:ss') ")
				.append("and FAB_CODE in ('ARY','CELL')")
				.append("group by FAB_CODE,USER_CODE,USER_NAME,USER_DEPT ");
		
		
        List<Object> paramList = new ArrayList<Object>();  
        //填充参数  
        paramList.add(fromDate);  
        paramList.add(toDate);
        JdbcUtil jdbcUtil = null;  
        try {  
            jdbcUtil = new JdbcUtil();  
            jdbcUtil.getConnection(); // 获取数据库链接  
            List<Map<String, Object>> mapList = jdbcUtil.findResult( selectSQL.toString(), paramList);  
            if(mapList.size()>0){  
            	for(int i = 0; i< mapList.size();i++) {
                 Map<String, Object> map = mapList.get(i);  
    				String user_code = (String) map.get("USER_CODE");
    				String user_name = (String) map.get("USER_NAME");
    				String user_dept = (String) map.get("USER_DEPT");
    				String strhour = map.get("HOURS").toString();
    				double hour = Double.parseDouble(strhour);
    				
    				PerformanceMon objPerformance = performanceService.getPerformanceMon(month, user_code);
    				if(objPerformance !=null && objPerformance.getOpe_fk() !=null){
    					Dustfree dustfree = new Dustfree();
    					dustfree.setMonth(month);
    					dustfree.setFactory(objPerformance.getFactory());
    					dustfree.setEmp_id(user_code);
    					dustfree.setEmp_name(user_name);
    					dustfree.setDept_name(user_dept);
    					dustfree.setOpe_fk(objPerformance.getOpe_fk());
    					dustfree.setHour(hour);
    					if(hour>=140)dustfree.setAmount(100.0);
    					else dustfree.setAmount(0.0);
    					dustfree.setMemo("");
    					dustfreeManager.save(dustfree);
	    				}
	            }  
            }
        } catch (SQLException e) {  
            System.out.println(this.getClass()+"执行查询操作抛出异常！");  
            e.printStackTrace();  
        } finally {  
            if (jdbcUtil != null) {  
                jdbcUtil.releaseConn(); // 一定要释放资源  
            }  
		logger.info("同步ary和cel厂的数据结束******************************************");
		
		logger.info("同步模组厂的数据开始******************************************");
		StringBuilder hql = 
				new StringBuilder("select new map(p.month as month,p.factory as factory,p.emp_id as emp_id,p.emp_name as emp_name,p.department_id as department_id,p.department_name as department_name,")
        		.append(" p.dateOfRegister as dateOfRegister,c.id as id,c.name as ope_id,c.label as ope_name,p.attdHour as hour,")
        		.append(" case when p.attdHour>=140 and c.id in(191 ,193, 195, 203,194,196,204,346,347,351,352,354,355,356) then '100.0' when p.attdHour<140 then '0.0' else '50.0' end as amount) from PerformanceMon p")
                .append(" left join Code c on p.ope_fk=c.id")
                .append(" where p.isValid is true and c.isValid is true and p.factory in ('M1','M2','LCM') and p.month = ?0 "); 
		String queryString = hql.toString();
		List<Map> list = hibernateInvoker.find(Map.class,queryString,month);
		for(Map obj:list){
			Dustfree dustfree = new Dustfree();
			dustfree.setMonth((String)obj.get("month"));
			dustfree.setFactory((String)obj.get("factory"));
			dustfree.setEmp_id((String)obj.get("emp_id"));
			dustfree.setEmp_name((String)obj.get("emp_name"));
			dustfree.setDept_name((String)obj.get("department_name"));
			dustfree.setOpe_fk((Long) (obj.get("id")));
			dustfree.setHour((Double)(obj.get("hour")));
			dustfree.setAmount(Double.valueOf((String) obj.get("amount")));
			dustfree.setMemo("");
			dustfreeManager.save(dustfree);
		}
		
		logger.info("同步模组厂的数据结束******************************************");
		
		logger.info("DustfreeQuartz 结束同步******************************************");
	}
	}
}
